﻿namespace uMember
{
    public static class uMembershipConstants
    {
        #region SQL Queries

        //TODO: Need to refactor this huge chunk of SQL!
        /// <summary>
        /// Gets the members by a property value
        /// </summary>
        public static string QuerySearchAllMembersByPropertySQL = @"
                select *, count(*) OVER() AS 'Count' from
                (SELECT MEMBTYPES.Alias AS MemberFieldAlias,  
                    ISNULL(CASE 
                    WHEN MEMBTYPES.datatypeID IN (SELECT NodeId FROM DBO.CMSDATATYPE WHERE DBTYPE = 'Ntext') THEN MEMBDATA.[dataNtext] 
                    WHEN MEMBTYPES.datatypeID IN (SELECT NodeId FROM DBO.CMSDATATYPE WHERE DBTYPE = 'Nvarchar') THEN MEMBDATA.[dataNvarchar] 
                    WHEN MEMBTYPES.datatypeID IN (SELECT NodeId FROM DBO.CMSDATATYPE WHERE DBTYPE = 'Date') THEN CONVERT(NVARCHAR, MEMBDATA.[dataDate]) 
                    WHEN MEMBTYPES.datatypeID IN (SELECT NodeId FROM DBO.CMSDATATYPE WHERE DBTYPE = 'Integer') THEN CONVERT(NVARCHAR, MEMBDATA.[dataInt]) 
                    ELSE NULL END, NULL) 
                    AS MemberData, MEMB.LoginName, MEMB.Email, MEMBNODE.createDate, MEMB.nodeId, MEMBGROUPNODE.text As MemberGroup, MEMBNODE.text as MemberName
                    FROM 
                    (SELECT id, text, createDate, nodeUser FROM dbo.umbracoNode WHERE (nodeObjectType = '9b5416fb-e72f-45a9-a07b-5a9a2709ce43')) AS MEMBTYPEID 
                    LEFT OUTER JOIN (SELECT nodeId, contentType FROM dbo.cmsContent) AS MEMBLST ON MEMBLST.contentType = MEMBTYPEID.id 
                    LEFT OUTER JOIN dbo.cmsPropertyType AS MEMBTYPES ON MEMBTYPES.contentTypeId = MEMBLST.contentType 
                    LEFT OUTER JOIN dbo.cmsPropertyData AS MEMBDATA ON MEMBDATA.contentNodeId = MEMBLST.nodeId AND MEMBDATA.propertytypeid = MEMBTYPES.id 
                    LEFT OUTER JOIN dbo.cmsMember AS MEMB ON MEMB.nodeId = MEMBLST.nodeId 
                    LEFT OUTER JOIN dbo.cmsMember2MemberGroup AS MEMBGROUP ON MEMBGROUP.Member = MEMB.nodeId
                    LEFT OUTER JOIN dbo.umbracoNode AS MEMBGROUPNODE ON MEMBGROUPNODE.id = MEMBGROUP.MemberGroup
                    LEFT OUTER JOIN dbo.umbracoNode AS MEMBNODE ON MEMBNODE.id = MEMB.nodeId) 
                 Src2
                where Src2.nodeId IN 

                (Select nodeId  from

                 (SELECT MEMBTYPES.Alias AS MemberFieldAlias,  
                    ISNULL(CASE 
                    WHEN MEMBTYPES.datatypeID IN (SELECT NodeId FROM DBO.CMSDATATYPE WHERE DBTYPE = 'Ntext') THEN MEMBDATA.[dataNtext] 
                    WHEN MEMBTYPES.datatypeID IN (SELECT NodeId FROM DBO.CMSDATATYPE WHERE DBTYPE = 'Nvarchar') THEN MEMBDATA.[dataNvarchar] 
                    WHEN MEMBTYPES.datatypeID IN (SELECT NodeId FROM DBO.CMSDATATYPE WHERE DBTYPE = 'Date') THEN CONVERT(NVARCHAR, MEMBDATA.[dataDate]) 
                    WHEN MEMBTYPES.datatypeID IN (SELECT NodeId FROM DBO.CMSDATATYPE WHERE DBTYPE = 'Integer') THEN CONVERT(NVARCHAR, MEMBDATA.[dataInt]) 
                    ELSE NULL END, NULL) 
                    AS MemberData, MEMB.LoginName, MEMB.Email, MEMBNODE.createDate, MEMB.nodeId, MEMBGROUPNODE.text As MemberGroup, MEMBNODE.text as MemberName
                    FROM 
                    (SELECT id, text, createDate, nodeUser FROM dbo.umbracoNode WHERE (nodeObjectType = '9b5416fb-e72f-45a9-a07b-5a9a2709ce43')) AS MEMBTYPEID 
                    LEFT OUTER JOIN (SELECT nodeId, contentType FROM dbo.cmsContent) AS MEMBLST ON MEMBLST.contentType = MEMBTYPEID.id 
                    LEFT OUTER JOIN dbo.cmsPropertyType AS MEMBTYPES ON MEMBTYPES.contentTypeId = MEMBLST.contentType 
                    LEFT OUTER JOIN dbo.cmsPropertyData AS MEMBDATA ON MEMBDATA.contentNodeId = MEMBLST.nodeId AND MEMBDATA.propertytypeid = MEMBTYPES.id 
                    LEFT OUTER JOIN dbo.cmsMember AS MEMB ON MEMB.nodeId = MEMBLST.nodeId 
                    LEFT OUTER JOIN dbo.cmsMember2MemberGroup AS MEMBGROUP ON MEMBGROUP.Member = MEMB.nodeId
                    LEFT OUTER JOIN dbo.umbracoNode AS MEMBGROUPNODE ON MEMBGROUPNODE.id = MEMBGROUP.MemberGroup
                    LEFT OUTER JOIN dbo.umbracoNode AS MEMBNODE ON MEMBNODE.id = MEMB.nodeId) 
                 Src ";

        /// <summary>
        /// Get all members by a property and value
        /// </summary>
        public static string QuerySearchAllMembersByPropertyValue = string.Format(@"{0} WHERE Src.MemberFieldAlias = @propertyName AND MemberData Like @search) ORDER BY nodeId", QuerySearchAllMembersByPropertySQL);

        /// <summary>
        /// Finds all members by a property which value which is not null
        /// </summary>
        public static string QuerySearchAllMembersByPropertyValueNotNull = string.Format(@"{0} WHERE Src.MemberFieldAlias = @propertyName AND MemberData IS NOT NULL) ORDER BY nodeId", QuerySearchAllMembersByPropertySQL);

        /// <summary>
        /// Finds all members by a property which value which is null
        /// </summary>
        public static string QuerySearchAllMembersByPropertyValueIsNull = string.Format(@"{0} WHERE Src.MemberFieldAlias = @propertyName AND MemberData IS NULL) ORDER BY nodeId", QuerySearchAllMembersByPropertySQL);

        /// <summary>
        /// Search members by login name
        /// </summary>
        public static string QuerySearchMembersByLogin = string.Format(@"{0} WHERE (MEMB.LoginName like @search) ORDER BY MEMB.nodeId DESC", MainSqlBulk);

        /// <summary>
        /// Search members by create date range
        /// </summary>
        public static string QuerySearchMembersByDateRange = string.Format(@"{0} WHERE MEMBNODE.createDate between @dateFrom and @dateTo ORDER BY MEMB.nodeId DESC", MainSqlBulk);

        /// <summary>
        /// Get a member by id
        /// </summary>
        public static string QueryGetSingleMemberById = string.Format(@"{0} WHERE (MEMBLST.nodeId = @memberId)", MainSqlBulk);

        /// <summary>
        /// Get a single member by their email address
        /// </summary>
        public static string QueryGetSingleMemberByEmail = string.Format(@"{0} WHERE (MEMB.Email = @email)", MainSqlBulk);

        /// <summary>
        /// Get a single member by their login name
        /// </summary>
        public static string QueryGetSingleMemberByLogin = string.Format(@"{0} WHERE (MEMB.LoginName = @loginname)", MainSqlBulk);

        /// <summary>
        /// Sql to get all members by group name
        /// </summary>
        public static string QueryGetAllMembersByGroupName = string.Format(@"{0} WHERE (MEMBGROUPNODE.text Like @group) ORDER BY MEMB.nodeId DESC", MainSqlBulk);

        /// <summary>
        /// Sql to get all members
        /// </summary>
        public static string QueryGetAllMembers = string.Format(@"{0} ORDER BY MEMB.nodeId DESC", MainSqlBulk);

        /// <summary>
        /// Sql to get all members by CSV
        /// </summary>
        public static string QueryGetAllMembersCsv(string csv)
        {
            return string.Format(@"{0} WHERE MEMB.nodeId IN ({1}) ORDER BY MEMB.nodeId DESC", MainSqlBulk, csv);
        }

        #endregion

        /// <summary>
        /// The main bulk of the SQL that does the work
        /// </summary>
        private const string MainSqlBulk = @"SELECT count(*) OVER() AS 'Count', MEMBTYPES.Alias AS MemberFieldAlias,  
                ISNULL(CASE 
                WHEN MEMBTYPES.datatypeID IN (SELECT NodeId FROM DBO.CMSDATATYPE WHERE DBTYPE = 'Ntext') THEN MEMBDATA.[dataNtext] 
                WHEN MEMBTYPES.datatypeID IN (SELECT NodeId FROM DBO.CMSDATATYPE WHERE DBTYPE = 'Nvarchar') THEN MEMBDATA.[dataNvarchar] 
                WHEN MEMBTYPES.datatypeID IN (SELECT NodeId FROM DBO.CMSDATATYPE WHERE DBTYPE = 'Date') THEN CONVERT(NVARCHAR, MEMBDATA.[dataDate]) 
                WHEN MEMBTYPES.datatypeID IN (SELECT NodeId FROM DBO.CMSDATATYPE WHERE DBTYPE = 'Integer') THEN CONVERT(NVARCHAR, MEMBDATA.[dataInt]) 
                ELSE NULL END, NULL) 
                AS MemberData, MEMB.LoginName, MEMB.Email, MEMBNODE.createDate, MEMB.nodeId, MEMBGROUPNODE.text As MemberGroup, MEMBNODE.text as MemberName
                FROM 
                (SELECT id, text, createDate, nodeUser FROM dbo.umbracoNode WHERE (nodeObjectType = '9b5416fb-e72f-45a9-a07b-5a9a2709ce43')) AS MEMBTYPEID 
                LEFT OUTER JOIN (SELECT nodeId, contentType FROM dbo.cmsContent) AS MEMBLST ON MEMBLST.contentType = MEMBTYPEID.id 
                LEFT OUTER JOIN dbo.cmsPropertyType AS MEMBTYPES ON MEMBTYPES.contentTypeId = MEMBLST.contentType 
                LEFT OUTER JOIN dbo.cmsPropertyData AS MEMBDATA ON MEMBDATA.contentNodeId = MEMBLST.nodeId AND MEMBDATA.propertytypeid = MEMBTYPES.id 
                LEFT OUTER JOIN dbo.cmsMember AS MEMB ON MEMB.nodeId = MEMBLST.nodeId 
                LEFT OUTER JOIN dbo.cmsMember2MemberGroup AS MEMBGROUP ON MEMBGROUP.Member = MEMB.nodeId
                LEFT OUTER JOIN dbo.umbracoNode AS MEMBGROUPNODE ON MEMBGROUPNODE.id = MEMBGROUP.MemberGroup
                LEFT OUTER JOIN dbo.umbracoNode AS MEMBNODE ON MEMBNODE.id = MEMB.nodeId";


    }
}